﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
//using System.Windows.Forms;
using System.Configuration;
namespace DAO
{
    public class DataService
    {
        private string _connectionstring = ConfigurationManager.ConnectionStrings["aspnetdbConnectionString"].ConnectionString;
        private SqlConnection _conn = null;

        public SqlConnection Connection
        {
            get { return _conn; }
            set { _conn = value; }
        }
        public string ConnectionString
        {
            get
            {
                return _connectionstring;
            }
            set
            {
                _connectionstring = value;
            }
        }

        public int GetIdentityInsertValue(string table)
        {
            SqlCommand _command = new SqlCommand(string.Format("SELECT IDENT_CURRENT('{0}')", table), _conn);

            return int.Parse(_command.ExecuteScalar().ToString());

        }
        public DataService()
        {
            _connectionstring = Configuration.GetConnectionString();
        }

        public void Connect()
        {
            try
            {
                _conn = new SqlConnection(_connectionstring);
                _conn.Open();
            }
            catch (Exception e)
            {
                throw new Exception("Không kết nối được cơ sở dữ liệu !!! " + e.Message);
            }
        }

        public void Disconnect()
        {
            _conn.Close();
            _conn = null;
        }

        public bool IsConnect()
        {
            return (_conn != null);
        }

        public SqlDataReader ExecuteQuery(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, _conn);

            return _command.ExecuteReader();

        }

        public object ExecuteScalar(string strSQL)
        {
            SqlCommand _command = new SqlCommand(strSQL, _conn);

            return _command.ExecuteScalar();
        }

        public int ExecuteNoneQuery(string strSQL)
        {

            SqlCommand _command = new SqlCommand(strSQL, _conn);
            int i = _command.ExecuteNonQuery();
            return i;
        }

        public DataSet ExcuteQueryDataSet(string strSQL)
        {
            SqlDataAdapter adapter = new SqlDataAdapter(strSQL, _conn);
            DataSet ds = new DataSet("DATASET");
            adapter.Fill(ds);
            return ds;
        }
        public DataTable ExcuteQueryDataTable(string strSQL)
        {
            DataSet ds = ExcuteQueryDataSet(strSQL);
            return ds.Tables[0];
        }
        public int ExcuteProcedureWithReturn(string procName, SqlParameter[] parameters)
        {
            try
            {
                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlParameter returnVal = new SqlParameter("returnVal", SqlDbType.Int);
                returnVal.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnVal);
                command.ExecuteNonQuery();
                return Convert.ToInt32(returnVal.Value);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() " + ex.Message);
            }
        }

        public DataTable ExcuteProcedureGetTable(string procName, SqlParameter[] parameters, SqlTransaction tran)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                command.Transaction = tran;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!" + ex.Message);
            }
        }

        public void ExcuteProcedure(string procName, SqlParameter[] parameters, SqlTransaction tran)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                command.Transaction = tran;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                command.ExecuteNonQuery();
            }
            catch
            {
            }
        }
        public DataSet ExcuteProcedureGetDataset(string procName, SqlParameter[] parameters, SqlTransaction tran)
        {
            try
            {

                SqlCommand command = new SqlCommand();

                command.Connection = _conn;

                command.CommandText = procName;

                command.CommandType = CommandType.StoredProcedure;

                command.Transaction = tran;

                if (parameters != null)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        command.Parameters.Add(para);
                    }
                }

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet("DATASET");
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi DataService.ExcuteProcedureGetDataset() !!!" + ex.Message);
            }
        }
    }
}
